clear all
set more off, perm
set maxvar 120000
set varabbrev off
* ---------------------------------------------- *
global dir 	"`1'"
global Data 	$dir/Data
global Tables 	$dir/Tables
global Figures 	$dir/Figures
global Work 	$dir/Work
global Temp 	$dir/Temp
global Pseudo	$dir/Work/Temp
* ---------------------------------------------- *
use $Work/EBR_new_monthly, clear
drop SIZE
rename SI10 SIZE
keep permco permno datem BOOK SIZE INV* PROF MOME qruf1 qnic* ret_mo*
gstats winsor qnic*, cuts(1 99) by(datem) replace
* -------------------------------------------- *
tempfile tmpstuff
save "`tmpstuff'", replace
* -------------------------------------------- *
foreach PORTF in BOOK SIZE INV PROF MOME {	
use if `PORTF'+qruf1<. using "`tmpstuff'", clear
generate portf=1+int((`PORTF'-1)/2)
order *, sequential    
keep permco permno datem portf ret_mo1-ret_mo60 qnic*

gcollapse (mean) ret_mo1-ret_mo60 qnic*, by(datem portf)
* -------------------------------------------- *
gen  cumRrawMo1=(ret_mo1)^12-1
gen  cumQrawMo1=(1+qnic1)^12-1
* -------------------------------------------- *
gen  cumRrawQr1=1
gen  cumQrawQr1=1
forvalues x=1(1)3 {
	replace cumRrawQr1=cumRrawQr1*ret_mo`x'
	replace cumQrawQr1=cumQrawQr1*(1+qnic`x')
}
replace cumRrawQr1=cumRrawQr1^4-1
replace cumQrawQr1=cumQrawQr1^4-1
* -------------------------------------------- *
gen  cumRrawYr1=1 
gen  cumQrawYr1=1 
forvalues x=1(1)12 {
	replace cumRrawYr1=cumRrawYr1*ret_mo`x'
	replace cumQrawYr1=cumQrawYr1*(1+qnic`x')
}
replace cumRrawYr1=cumRrawYr1^1-1
replace cumQrawYr1=cumQrawYr1^1-1
* -------------------------------------------- *
gen  cumRrawYr3=1 
gen  cumQrawYr3=1 
forvalues x=1(1)36 {
	replace cumRrawYr3=cumRrawYr3*ret_mo`x'
	replace cumQrawYr3=cumQrawYr3*(1+qnic`x')
}
replace cumRrawYr3=(cumRrawYr3)^(1/3)-1
replace cumQrawYr3=(cumQrawYr3)^(1/3)-1
* -------------------------------------------- *
gen  cumRrawYr5=1 
gen  cumQrawYr5=1 
forvalues x=1(1)60 {
	replace cumRrawYr5=cumRrawYr5*ret_mo`x'
	replace cumQrawYr5=cumQrawYr5*(1+qnic`x')
}
replace cumRrawYr5=(cumRrawYr5)^(1/5)-1
replace cumQrawYr5=(cumQrawYr5)^(1/5)-1
* -------------------------------------------- *
keep portf datem cumR* cumQ* 

tempfile tmp
save "`tmp'", replace
*********************
matrix ret = J(5,2,.)
matrix ebr = J(5,2,.)

forvalues p=1(4)5  {
local i=1
foreach var in Mo1 Qr1 Yr1 Yr3 Yr5  {
	local w=(`p'==1)+2*(`p'==5)
	quietly: regress cumQraw`var' if portf==`p'
	matrix ebr[`i',`w']=_b[_cons]

	quietly: regress cumRraw`var' if portf==`p'
	matrix ret[`i',`w']=_b[_cons]
	local i=`i'+1
	}
}
putexcel set $Tables/AppendixB11_June.xlsx, sheet(`PORTF') modify
putexcel A2="1Mo"
putexcel A3="3Mo"
putexcel A4="1Yr"
putexcel A5="3Yr"
putexcel A6="5Yr"

putexcel A9 ="1Mo"
putexcel A10="3Mo"
putexcel A11="1Yr"
putexcel A12="3Yr"
putexcel A13="5Yr"

putexcel B1="LOW  `PORTF' Actual"
putexcel C1="HIGH `PORTF' Actual"

putexcel B8="LOW  `PORTF' EBRD"
putexcel C8="High `PORTF' EBRD"

putexcel B2=matrix(ret) , nformat(percent_d2)
putexcel B9=matrix(ebr) , nformat(percent_d2)
}
